package cn.com.cc.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
 * poi导出Excel模版
 * @author cc
 *
 */
public class ExcelOfTemplete {
	
	private HSSFWorkbook workBook;
	
	private HSSFSheet sheet;
	
	private HSSFRow row;
	
	private String xslFileName;
	
	private String xslFilePath;
	
	private static  String DATE_FORMAT = "yyyy/m/d";
	
	private static String NUMBER_FORMAT = "#,##0.00";
	
	private HttpServletResponse response;
	/**
	 * 适用网络版,传入导出后文件的文件名和response
	 * @param response
	 * @param fileName
	 */
	public ExcelOfTemplete(HttpServletResponse response, String fileName){
		this.response = response;
		this.workBook = new HSSFWorkbook();
		this.sheet = workBook.createSheet();
		this.xslFileName = fileName;
	}
	/**
	 * 适用单机版,根据文件路径自动生成文件
	 * @param xslFilePath
	 */
	public ExcelOfTemplete(String xslFilePath){
		this.workBook = new HSSFWorkbook();
		this.sheet = workBook.createSheet();
		this.xslFilePath = xslFilePath;
	}
	public void exportXLSFromFilePath() throws IOException{
		FileOutputStream out = new FileOutputStream(xslFilePath);
		workBook.write(out);
		out.flush();
		out.close();
	}
	
	public void exportXLSFromFileName() throws IOException{
		if(response == null){
			throw new NullPointerException("response must not be null");
		}
		if(xslFileName == null || "".equals(xslFileName.trim())){
			throw new NullPointerException("export filename must not be null");
		}
		response.reset();
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment; filename=" + (new String(xslFileName.getBytes(), "iso-8859-1")) + ".xls");
		OutputStream out = response.getOutputStream();
		workBook.write(out);
		out.flush();
		out.close();
	}
	
	
	public void crateRow(int index){
		this.row = this.sheet.createRow(index);
	}
	
	public void setColumnWidth(int index, int width) {
		this.sheet.setColumnWidth(index, width);
	}
	
	public void setBoldCell(int index, String value, int width) {
		this.sheet.setColumnWidth(index, width);
		HSSFCell cell = this.row.createCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
		HSSFFont font = workBook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellStyle.setFont(font);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//
		cell.setCellValue(HtmlToTextGb2312(value));
		cell.setCellStyle(cellStyle);
	}
	
	public void setTitleCell(int index, String value, int width) {
		this.sheet.setColumnWidth(index, width);
		HSSFCell cell = this.row.createCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
		HSSFFont font = workBook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short)18);
		cellStyle.setFont(font);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//
		cell.setCellValue(HtmlToTextGb2312(value));
		cell.setCellStyle(cellStyle);
	}
	/**
	 * 字符串类型的内容
	 * @param index
	 * @param value
	 */
	public void setCell(int index, String value) {
		HSSFCell cell = this.row.createCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
		cellStyle.setWrapText(true);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//
		cell.setCellValue(HtmlToTextGb2312(value));
		cell.setCellStyle(cellStyle);
	}
	/**
	 * 日期类的内容,并设置显示格式
	 * @param index
	 * @param value
	 */
	public void setCell(int index, Calendar value) {
		HSSFCell cell = this.row.createCell(index);
		cell.setCellValue(value.getTime());
		HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		cellStyle.setDataFormat(workBook.createDataFormat().getFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
		cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
	}
	/**
	 * 设置整数类型的内容
	 * @param index
	 * @param value
	 */
	public void setCell(int index, int value) {
		HSSFCell cell = this.row.createCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//
		cell.setCellValue(value);
		cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
	}
	/**
	 * 设置浮点类型的内容,并设置浮点格式
	 * @param index
	 * @param value
	 */
	public void setCell(int index, double value) {
		HSSFCell cell = this.row.createCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		HSSFCellStyle cellStyle = workBook.createCellStyle(); // 建立新的cell样式
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		HSSFDataFormat format = workBook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
		cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
	}
	
	/**
	 * 内容去除html标签,空格,制表符,回车符等
	 * @param inputString
	 * @return
	 */
	public String HtmlToTextGb2312(String inputString) {
		String htmlStr = inputString; // 含html标签的字符串
		String textStr = "";
		Pattern p_script;
		Matcher m_script;
		Pattern p_style;
		Matcher m_style;
		Pattern p_html;
		Matcher m_html;
		Pattern p_houhtml;
		Matcher m_houhtml;
		Pattern p_spe;
		Matcher m_spe;
		Pattern p_blank;
		Matcher m_blank;
		Pattern p_table;
		Matcher m_table;
		Pattern p_enter;
		Matcher m_enter;

		try {
			// 定义script的正则表达式.
			String regEx_script = "<[\\s]*?script[^>]*?>[\\s\\S]*?<[\\s]*?\\/[\\s]*?script[\\s]*?>";
			// 定义style的正则表达式.
			String regEx_style = "<[\\s]*?style[^>]*?>[\\s\\S]*?<[\\s]*?\\/[\\s]*?style[\\s]*?>";
			// 定义HTML标签的正则表达式
			String regEx_html = "<[^>]+>";
			// 定义HTML标签的正则表达式
			String regEx_houhtml = "/[^>]+>";
			// 定义特殊符号的正则表达式
			String regEx_spe = "\\&[^;]+;";
			// 定义多个空格的正则表达式
			String regEx_blank = " +";
			// 定义多个制表符的正则表达式
			String regEx_table = "\t+";
			// 定义多个回车的正则表达式
			String regEx_enter = "\n+";

			p_script = Pattern.compile(regEx_script, Pattern.CASE_INSENSITIVE);
			m_script = p_script.matcher(htmlStr);
			htmlStr = m_script.replaceAll(""); // 过滤script标签

			p_style = Pattern.compile(regEx_style, Pattern.CASE_INSENSITIVE);
			m_style = p_style.matcher(htmlStr);
			htmlStr = m_style.replaceAll(""); // 过滤style标签

			p_html = Pattern.compile(regEx_html, Pattern.CASE_INSENSITIVE);
			m_html = p_html.matcher(htmlStr);
			htmlStr = m_html.replaceAll(""); // 过滤html标签

			p_houhtml = Pattern
					.compile(regEx_houhtml, Pattern.CASE_INSENSITIVE);
			m_houhtml = p_houhtml.matcher(htmlStr);
			htmlStr = m_houhtml.replaceAll(""); // 过滤html标签

			p_spe = Pattern.compile(regEx_spe, Pattern.CASE_INSENSITIVE);
			m_spe = p_spe.matcher(htmlStr);
			htmlStr = m_spe.replaceAll(""); // 过滤特殊符号

			p_blank = Pattern.compile(regEx_blank, Pattern.CASE_INSENSITIVE);
			m_blank = p_blank.matcher(htmlStr);
			htmlStr = m_blank.replaceAll(" "); // 过滤过多的空格

			p_table = Pattern.compile(regEx_table, Pattern.CASE_INSENSITIVE);
			m_table = p_table.matcher(htmlStr);
			htmlStr = m_table.replaceAll(" "); // 过滤过多的制表符

			p_enter = Pattern.compile(regEx_enter, Pattern.CASE_INSENSITIVE);
			m_enter = p_enter.matcher(htmlStr);
			htmlStr = m_enter.replaceAll(" "); // 过滤过多的制表符

			textStr = htmlStr;

		} catch (Exception e) {
			System.err.println("Html2Text: " + e.getMessage());
		}

		return textStr;// 返回文本字符串
	}
	
	
	public static void main(String[] args) throws IOException{
		ExcelOfTemplete e = new ExcelOfTemplete("d:\\a.xls");
		e.crateRow(0);
		e.setTitleCell(0, "序号", 5000);
		e.setTitleCell(1, "姓名", 5000);
		e.setTitleCell(2, "分数", 5000);
		e.setTitleCell(3, "时间", 5000);
		
		e.crateRow(1);
		e.setCell(0, 1);
		e.setCell(1, "陈冲");
		e.setCell(2, 95.5);
		e.setCell(3, Calendar.getInstance());
		e.exportXLSFromFilePath();
	}

}
